/**
 * Repositorio parcela com os principais metodos
 * 
 * @author			Paulo Lima
 * @corporation 	TeckSoft 2014
 * @project 		Araujo Seguros
 * @version 		1.0 
 * @since			Release 1  05/03/2014
 */
package repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import models.Parcela;
import models.ParcelaVencida;
import util.ConnectionFactory;

public class RepositorioParcela implements IRepositorioParcela {

	// ---------------------------------------------------------------------------------
	public boolean inserirParcela(Parcela parcela) throws SQLException {
		String sql = "insert into parcela values (?,?,?,?,?)";

		try {

			Connection conIntranet = new ConnectionFactory().getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, 0);
			stm1.setString(2, parcela.getDataVencimento());
			stm1.setString(3, parcela.getValor());
			stm1.setString(4, parcela.getDataAviso());
			stm1.setInt(5, parcela.getPagamentoId());
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public boolean removerParcela(int idPagamento) throws SQLException {

		String sql = "DELETE FROM parcela WHERE pagamento_id=?";

		try {
			Connection conIntranet = new ConnectionFactory().getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, idPagamento);
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}
	}

	// ---------------------------------------------------------------------------------
	public boolean atualizarParcela(Parcela parcela) throws SQLException {
		String sql = "UPDATE parcela SET "
				+ "data_vencimento=?, valor=?, data_aviso=?, pagamento_id=?"
				+ " WHERE id=?";

		try {

			Connection conIntranet = new ConnectionFactory().getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setString(1, parcela.getDataVencimento());
			stm1.setString(2, parcela.getValor());
			stm1.setString(3, parcela.getDataAviso());
			stm1.setInt(4, parcela.getPagamentoId());
			stm1.setInt(5, parcela.getId());
			stm1.executeUpdate();
			stm1.close();
			conIntranet.close();
			return true;
		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public List<Parcela> listarParcela() throws SQLException {

		ArrayList<Parcela> parcelaDB = new ArrayList<>();
		Parcela parcela;
		String sql = "SELECT * FROM parcela";

		try {
			Connection conIntranet = new ConnectionFactory().getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				parcela = new Parcela();
				parcela.setId(rs1.getInt(1));
				parcela.setDataVencimento(rs1.getString(2));
				parcela.setValor(rs1.getString(3));
				parcela.setDataAviso(rs1.getString(4));
				parcela.setPagamentoId(rs1.getInt(5));
				parcelaDB.add(parcela);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {

			new SQLException();
		}

		return parcelaDB;
	}

	// ---------------------------------------------------------------------------------
	public List<Parcela> buscarParcela(String chave) throws SQLException {
		ArrayList<Parcela> parcelaDB = new ArrayList<>();
		Parcela parcela;
		String sql = "SELECT * FROM parcela WHERE status LIKE '%" + chave
				+ "%'";

		try {
			Connection conIntranet = new ConnectionFactory().getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				parcela = new Parcela();
				parcela.setId(rs1.getInt(1));
				parcela.setDataVencimento(rs1.getString(2));
				parcela.setValor(rs1.getString(3));
				parcela.setDataAviso(rs1.getString(4));
				parcela.setPagamentoId(rs1.getInt(5));
				parcelaDB.add(parcela);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {

			new SQLException();
		}

		return parcelaDB;
	}

	// ---------------------------------------------------------------------------------
	public List<Parcela> retornaParcelas(int idPagamento) throws SQLException {

		ArrayList<Parcela> parcelaDB = new ArrayList<>();
		Parcela parcela;
		String sql = "SELECT * FROM parcela WHERE pagamento_id = "
				+ idPagamento + "";

		try {
			Connection conIntranet = new ConnectionFactory().getConnection();
			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				parcela = new Parcela();
				parcela.setId(rs1.getInt(1));
				parcela.setDataVencimento(rs1.getString(2));
				parcela.setValor(rs1.getString(3));
				parcela.setDataAviso(rs1.getString(4));
				parcela.setPagamentoId(rs1.getInt(5));
				parcelaDB.add(parcela);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {

			new SQLException();
		}

		return parcelaDB;
	}

	// ---------------------------------------------------------------------------------
	public List<ParcelaVencida> parcelasVencidas() throws SQLException {

		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); // formato
																	// para a
																	// Data
		sdf.setLenient(false); // validar a data
		Calendar data = Calendar.getInstance(); // instancia calendar nativo
												// JAVA
		String dataAtual = sdf.format(data.getTime()); // converter a data para
														// String
		Date dataSistema = null;
		try {
			dataSistema = sdf.parse(dataAtual);
		} catch (ParseException e1) {
			e1.printStackTrace();
		}

		ArrayList<ParcelaVencida> parcelaDB = new ArrayList<>();
		//Parcela parcela;
		ParcelaVencida parcela;
		// String sql = "SELECT * FROM parcela ";

		String sql = "SELECT cliente.id, cliente.nome, pagamento.premio_total, "
				+ "pagamento.forma_pagamento, parcela.id, parcela.data_vencimento, "
				+ "parcela.valor, parcela.data_aviso, parcela.pagamento_id FROM parcela, "
				+ "cliente, pagamento WHERE cliente.id = pagamento.id AND "
				+ "pagamento.id = parcela.pagamento_id ORDER BY parcela.data_aviso ASC;";
		try {

			Connection conIntranet = new ConnectionFactory().getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);
			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {
				
				
				parcela = new ParcelaVencida();
				
				

				
				parcela = new ParcelaVencida();
				parcela.setIdCliente(rs1.getInt(1));
				parcela.setNomeCliente(rs1.getString(2));
				parcela.setPremioTotal(rs1.getString(3));
				parcela.setFormaPagamento(rs1.getString(4));
				parcela.setIdParcela(rs1.getInt(5));
				parcela.setDataVencimento(rs1.getString(6));
				parcela.setValor(rs1.getString(7));
				parcela.setDataAviso(rs1.getString(8));
				parcela.setPagamentoId(rs1.getInt(9));

				String dataAviso = parcela.getDataAviso();

				try {
					Date dataBDAviso = sdf.parse(dataAviso);
					Date dataBDVencimento = sdf.parse(parcela
							.getDataVencimento());

					if (dataSistema.after(dataBDAviso)
							|| dataSistema.equals(dataBDAviso)) {
						if (dataSistema.before(dataBDVencimento)
								|| dataSistema.equals(dataBDVencimento))
							parcelaDB.add(parcela);
					}

				} catch (ParseException e2) {
					e2.printStackTrace();
				}

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {
			new SQLException();
		}

		return parcelaDB;
	}
	// ---------------------------------------------------------------------------------
}
